class OrderDao {
  constructor(pool) {
    this.pool = pool;
  }

  async getOrdersByBuyer(userId) {
    const sql = `
      SELECT o.order_id, o.buyer_id, o.seller_id, o.created_at, o.status,nick_name,
            oi.product_id, oi.quantity, oi.remarks, oi.product_type,
            COALESCE(p.product_name, a.product_name) AS product_name,
            COALESCE(p.category, a.product_category) AS category,
            COALESCE(p.price, a.price_offer) AS price
      FROM orders o
      JOIN order_items oi ON o.order_id = oi.order_id
      JOIN users u ON o.seller_id = u.user_id
      LEFT JOIN agri_products p ON oi.product_type = 'purchase' AND oi.product_id = p.product_id
      LEFT JOIN articles a ON oi.product_type = 'supply' AND oi.product_id = a.article_id
    WHERE o.buyer_id = ?;

    `;
  
    const [rows] = await this.pool.execute(sql, [userId]);
    console.log(rows[0])
    return rows.map(row => ({
      order_id: row.order_id,
      product_id: row.product_id,
      product_name: row.product_name,
      category: row.category,
      quantity: row.quantity,
      price: row.price || 0,  // 避免 undefined
      remarks: row.remarks,
      order_created_at: row.created_at,  
      status: row.status,
      counterparty: row.nick_name,
      type: '收购'
    }));
  }
  

  async getOrdersBySeller(userId) {
    const sql = `
        SELECT o.order_id, o.buyer_id, o.seller_id, o.created_at, o.status,nick_name, 
              oi.product_id, oi.quantity, oi.remarks, oi.product_type,
              COALESCE(p.product_name, a.product_name) AS product_name,
              COALESCE(p.category, a.product_category) AS category,
              COALESCE(p.price, a.price_offer) AS price
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        JOIN users u ON o.buyer_id = u.user_id
        LEFT JOIN agri_products p ON oi.product_type = 'purchase' AND oi.product_id = p.product_id
        LEFT JOIN articles a ON oi.product_type = 'supply' AND oi.product_id = a.article_id
      WHERE o.seller_id = ?;
    `;
  
    const [rows] = await this.pool.execute(sql, [userId]);
  console.log(rows[0])
    return rows.map(row => ({
      order_id: row.order_id,
      product_id: row.product_id,
      product_name: row.product_name,
      category: row.category,
      quantity: row.quantity,
      price: row.price || 0,
      remarks: row.remarks,
      order_created_at: row.created_at,  
      status: row.status,
      counterparty: row.nick_name,
      type: '出售'
    }));
  }
  

  async createOrder({ buyer_id, seller_id, items }) {
    const conn = await this.pool.getConnection();
    try {
      await conn.beginTransaction();

      // 插入订单
      console.log('插入订单数据:', [buyer_id, seller_id]);
      // const orderSql = `INSERT INTO orders (buyer_id, seller_id, created_at, updated_at, status) VALUES (?, ?, NOW(), NOW(), 'active')`;
      // const [orderResult] = await conn.execute(orderSql, [buyer_id, seller_id]);
      // 生成东八区时间
      const now = new Date();
      const chinaTime = new Date(now.getTime() + 16 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');
      console.log(chinaTime)
      const orderSql = `INSERT INTO orders (buyer_id, seller_id, created_at, updated_at, status) VALUES (?, ?, ?, ?, 'active')`;
      const [orderResult] = await conn.execute(orderSql, [buyer_id, seller_id, chinaTime, chinaTime]);
      const order_id = orderResult.insertId;

      // 插入订单项
      const itemSql = `INSERT INTO order_items (order_id, product_id, product_type, quantity, remarks) VALUES (?, ?, ?, ?, ?)`;

      // 检查和处理每个订单项数据
      for (const item of items) {
        // 确保 item 的每个字段都有效
        const productId = item.product_id === undefined ? null : item.product_id;  // 如果是 undefined，则设置为 null
        const productType = item.product_type === undefined ? null : item.product_type;
        const quantity = item.quantity === undefined ? 0 : item.quantity;  // 默认数量为 0
        const remarks = item.remarks === undefined ? null : item.remarks;  // 如果是 undefined，则设置为 null

        // 检查必需字段
        if (!productId || !productType) {
          throw new Error('订单项缺少 product_id 或 product_type');
        }

        // 打印检查数据
        console.log('插入订单项数据:', [order_id, productId, productType, quantity, remarks]);

        // 执行插入
        await conn.execute(itemSql, [order_id, productId, productType, quantity, remarks]);
      }

      await conn.commit();
      return { 
        order_id, 
        order_created_at: chinaTime, 
        status: 'active', 
        items 
      };
    } catch (error) {
      await conn.rollback();
      console.error('创建订单失败:', error);
      throw error;
    } finally {
      conn.release();
    }
  }
}

module.exports = OrderDao;
